﻿/* 
 * Since 2008 - 2012
 */

package com.hh.bss.customer.dao.impl;

import static com.hh.bss.util.ObjectUtils.isNotEmpty;

import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Repository;

import com.hh.bss.auth.entity.SystemUser;
import com.hh.bss.auth.util.AuthSqlUtil;
import com.hh.bss.base.BaseHibernateDao;
import com.hh.bss.common.ConstantDic;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.cache.SystemUserCache;
import com.hh.bss.common.page.Page;
import com.hh.bss.customer.dao.CustomerDao;
import com.hh.bss.customer.entity.Customer;
import com.hh.bss.customer.vo.query.CustomerQuery;
import com.hh.bss.util.DateHelper;
import com.hh.bss.util.StringHelper;

@Repository
public class CustomerDaoImpl extends BaseHibernateDao<Customer, java.lang.Integer> implements CustomerDao<Customer, java.lang.Integer> {

	public Class getEntityClass() {
		return Customer.class;
	}

	public Page findPage(CustomerQuery query) {
		// XsqlBuilder syntax,please see
		// http://code.google.com/p/rapid-xsqlbuilder
		// [column]为字符串拼接, {column}为使用占位符.
		// [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接
		// [column] 为PageRequest的属性

		Class[] os = { CustomerQuery.class };
		Class property = org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
		if (property != null) {
			if ("java.lang.String".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), query.getQuery());

			} else if ("java.lang.Integer".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));

			} else if ("java.lang.Double".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
			} else if ("java.util.Date".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
			}
		}
		// 生成sql2的原因是为了不喜欢使用xsqlbuilder的同学，请修改生成器模板，删除本段的生成
		StringBuilder sql2 = new StringBuilder("select t from Customer t where 1=1 ");
		if (isNotEmpty(query.getId())) {
			sql2.append(" and  t.id = :id ");
		}
		if (isNotEmpty(query.getCompanyId())) {
			sql2.append(" and  t.companyId = :companyId ");
		}
		if (isNotEmpty(query.getJobProfile())) {
			sql2.append(" and  t.jobProfile like :jobProfile ");
			query.setJobProfile("%" + query.getJobProfile() + "%");

		}
		if (isNotEmpty(query.getSourceType())) {
			sql2.append(" and  t.sourceType like :sourceType ");
			query.setSourceType("%" + query.getSourceType() + "%");

		}
		if (isNotEmpty(query.getCreator())) {
			sql2.append(" and  t.creator like :creator ");
			query.setCreator("%" + query.getCreator() + "%");

		}
		if (isNotEmpty(query.getCreateDtBegin())) {
			sql2.append(" and  t.createDt >= :createDtBegin ");
		}
		if (isNotEmpty(query.getCreateDtEnd())) {
			sql2.append(" and  t.createDt <= :createDtEnd ");
		}
		if (isNotEmpty(query.getModifier())) {
			sql2.append(" and  t.modifier like :modifier ");
			query.setModifier("%" + query.getModifier() + "%");

		}
		if (isNotEmpty(query.getModifiedDtBegin())) {
			sql2.append(" and  t.modifiedDt >= :modifiedDtBegin ");
		}
		if (isNotEmpty(query.getModifiedDtEnd())) {
			sql2.append(" and  t.modifiedDt <= :modifiedDtEnd ");
		}
		if (isNotEmpty(query.getSortColumns())) {
			sql2.append(" order by :sortColumns ");
		}

		return pageQuery(sql2.toString(), query);
	}

	public Page findCustomerPage(CustomerQuery query) {
		// TODO Auto-generated method stub
		Class[] os = { CustomerQuery.class };
		Class property = org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
		if (property != null) {
			if ("java.lang.String".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), query.getQuery());

			} else if ("java.lang.Integer".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));

			} else if ("java.lang.Double".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
			} else if ("java.util.Date".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
			}
		}
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();

		/**
		 * select b.id,a.id companyId,a.name,a.abbr_name
		 * abbrName,if(length(trim(a.name))=0,a.full_name,a.name)
		 * disName,a.full_name
		 * fullName,a.industry,a.property,a.type,a.tel,a.fax,
		 * a.website,a.size,a.location
		 * ,a.address,a.postcode,a.profile,a.capital,a
		 * .background,b.creator,b.create_dt createDt,a.modifier,a.modified_dt
		 * modifiedDt,b.creator custCreator ,c.begin_d beginD,c.end_d endD
		 * ,d.BDP,d.BDM from company a inner join customer b on
		 * a.id=b.company_id inner join (select
		 * cc.id,cc.cust_id,cc.begin_d,cc.end_d from customer_contract cc group
		 * by cc.cust_id ) c on b.id = c.cust_id left join (select a1.cust_id,
		 * group_concat(( if (b1.role = 'BDP', c1.name, NULL))) BDP,
		 * group_concat(( IF (b1.role = 'BDM', c1.name, NULL))) BDM from
		 * customer_contract a1, customer_contract_staff b1, system_user c1
		 * where a1.id = b1.contract_id AND b1.account = c1.account group by
		 * a1.cust_id) d on b.id = d.cust_id
		 */
		sql.append(" select * from ( ");
		sql.append(" select b.id,a.id companyId,a.name,a.abbr_name abbrName,if(length(trim(a.name))=0,a.full_name,a.name) disName,a.full_name fullName,a.industry,a.property,a.type,a.tel,a.fax,a.website,a.size,a.location,a.address,a.postcode,a.profile,a.capital,a.background,a.integrity,a.culture,b.creator,b.create_dt createDt,a.modifier,a.modified_dt modifiedDt,b.creator custCreator ,c.begin_d beginD,c.end_d endD ,d.BDP,d.BDM from company a ");
		sql.append(" inner join customer b on a.id=b.company_id ");
		// sql.append(" inner join (select   cc.id,cc.cust_id,cc.begin_d,cc.end_d from customer_contract cc group by cc.cust_id ) c on b.id = c.cust_id ");
		// sql.append(" inner join (select a.id cust_id,min(b.begin_d) begin_d,max(b.end_d) end_d  from customer a left join customer_contract b on a.id=b.cust_id left join customer_contract_service_company c on a.id=c.cust_id where (b.id>0 or c.contract_id>0) and (b.begin_d<=now() and b.end_d>=now()) group by a.id ) c on b.id = c.cust_id ");
		sql.append("inner join ( select cc.cust_id,min(cc.begin_d) begin_d,max(cc.end_d) end_d  from (select b.cust_id,b.begin_d,b.end_d from customer_contract b where (b.begin_d<=now() and b.end_d>=now())  union all select c.cust_id,b.begin_d,b.end_d from customer_contract b,customer_contract_service_company c where b.id=c.contract_id and (b.begin_d<=now() and b.end_d>=now()) ) cc group by cc.cust_id ) c on b.id = c.cust_id ");
		sql.append(" left join (select a1.cust_id, group_concat(( if (b1.role = 'BDP', c1.name, NULL))) BDP, group_concat(( IF (b1.role = 'BDM', c1.name, NULL))) BDM from customer_contract a1, customer_contract_staff b1, system_user c1 where a1.id = b1.contract_id AND b1.account = c1.account group by a1.cust_id) d on b.id = d.cust_id  ");
		// sql.append(" from company a,customer b,(select   cc.id,cc.cust_id,cc.begin_d,cc.end_d from customer_contract cc group by cc.cust_id ) c  ");
		// sql.append(" , left join (select a1.cust_id, group_concat(( if (b1.role = 'BDP', c1.name, NULL))) BDP, group_concat(( IF (b1.role = 'BDM', c1.name, NULL))) BDM from customer_contract a1, customer_contract_staff b1, system_user c1 where a1.id = b1.contract_id AND b1.account = c1.account group by a1.cust_id) d on b.id = d.cust_id ");
		// sql.append(" left join customer_contract_staff c11 on c.id= c11.contract_id where role='BDM'");
		// LEFT JOIN customer_contract c on a1.id = c.cust_id;
		// where a.id=b.company_id and b.id = c.cust_id
		sql.append(" where a.id=b.company_id and b.id = c.cust_id and a.is_delete=0 ");
		sql.append(" ) t where 1=1 ");
		// sql.append("left join (customer_contract c on  t.id = c.cust_id) ");
		// sql.append(" and exists(select 1 from customer_contract t1 where t.id=t1.cust_id) ");
		if (isNotEmpty(query.getId())) {
			sql.append(" and  t.companyId = ? ");
			values.add(query.getId());

		}
		if (isNotEmpty(query.getCompanyId())) {
			sql.append(" and  t.companyId = ? ");
			values.add(query.getCompanyId());
		}
		if (isNotEmpty(query.getName())) {
			sql.append(" and  ( t.name like ? or t.fullName like ? or t.abbrName like ? ) ");
			values.add("%" + query.getName() + "%");
			values.add("%" + query.getName() + "%");
			values.add("%" + query.getName() + "%");

		}
		/*
		 * if(isNotEmpty(query.getFullName())) {
		 * sql.append(" and  t.fullName like ? ");
		 * values.add("%"+query.getFullName()+"%");
		 * 
		 * }
		 */
		if (isNotEmpty(query.getIndustry())) {
			sql.append(" and  t.industry = ? ");
			values.add(query.getIndustry());
		}
		if (isNotEmpty(query.getProperty())) {
			sql.append(" and  t.property = ? ");
			values.add(query.getProperty());
		}
		if (isNotEmpty(query.getType())) {
			sql.append(" and  t.type = ? ");
			values.add(query.getType());
		}
		if (isNotEmpty(query.getTel())) {
			sql.append(" and  t.tel like ? ");
			values.add("%" + query.getTel() + "%");

		}
		if (isNotEmpty(query.getWebsite())) {
			sql.append(" and  t.website like ? ");
			values.add("%" + query.getWebsite() + "%");

		}
		if (isNotEmpty(query.getLocation())) {
			sql.append(" and  t.location = ? ");
			values.add(query.getLocation());

		}

		if (isNotEmpty(query.getAddress())) {
			sql.append(" and  t.address like ? ");
			values.add("%" + query.getAddress() + "%");

		}
		if (isNotEmpty(query.getPostcode())) {
			sql.append(" and  t.postcode like ? ");
			values.add("%" + query.getPostcode() + "%");

		}
		if (isNotEmpty(query.getProfile())) {
			sql.append(" and  t.profile like ? ");
			values.add("%" + query.getProfile() + "%");

		}
		if (isNotEmpty(query.getSize())) {
			sql.append(" and  t.size = ? ");
			values.add(query.getSize());
		}
		if (isNotEmpty(query.getIntegrity())) {
			// integrity
			sql.append(" and  t.integrity = ? ");
			values.add(query.getIntegrity());
		}
		if (isNotEmpty(query.getExintegrity())) {
			sql.append(" and  not ifnull(t.integrity,0) in (").append(query.getExintegrity()).append(") ");
		}

		if (isNotEmpty(query.getCreator())) {
			sql.append(" and  t.creator = ? ");
			values.add(query.getCreator());
		}
		if (isNotEmpty(query.getCreateDtBegin())) {
			sql.append(" and  t.createDt >= ? ");
			values.add(query.getCreateDtBegin());
		}
		if (isNotEmpty(query.getCreateDtEnd())) {
			sql.append(" and  t.createDt < ? ");
			values.add(DateHelper.add(query.getCreateDtEnd(), DateHelper.DAY, 1));
		}
		if (isNotEmpty(query.getModifier())) {
			sql.append(" and  t.modifier = ? ");
			values.add(query.getModifier());
		}
		if (isNotEmpty(query.getModifiedDtBegin())) {
			sql.append(" and  t.modifiedDt >= ? ");
			values.add(query.getModifiedDtBegin());
		}
		if (isNotEmpty(query.getModifiedDtEnd())) {
			sql.append(" and  t.modifiedDt < ? ");
			values.add(DateHelper.add(query.getModifiedDtEnd(), DateHelper.DAY, 1));
		}

		if (isNotEmpty(query.getKeyword())) {

			// sql.append(" and  (t.website like ?  or t.address like ? or  t.postcode like ? or  t.tel like ? or t.background like ? )");
			sql.append(" and t.website like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.address like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.postcode like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.tel like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.background like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.culture like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.profile like ? ");
			values.add("%" + query.getKeyword() + "%");

		}
		if (isNotEmpty(query.getAuthType())) {
			if (ConstantDic.CUSTOMER_ALL.equals(query.getAuthType())) {
				if (query.getOnlineUser() != null) {
					String authsql = authSQL(query.getOnlineUser().getUsername(), "t.id", "t.custCreator");
					if (!StringHelper.isEmpty(authsql))
						sql.append(authsql);
				}
			} else {
				// 增加权限控制
				if (query.getOnlineUser() != null) {
					String authsql = authSQL(query.getOnlineUser().getUsername(), "t.id", "t.custCreator", query.getAuthType());
					if (!StringHelper.isEmpty(authsql))
						sql.append(authsql);
				}
			}

		}
		if (isNotEmpty(query.getSortColumns())) {
			sql.append(" order by  t.").append(query.getSortColumns());
		} else {
			sql.append(" order by  t.companyId desc  ");
		}
		System.out.println(" customer sql:" + sql);
		// values.add(id);
		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	public Page findBdCustomerPage(CustomerQuery query) {
		Class[] os = { CustomerQuery.class };
		Class property = org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
		if (property != null) {
			if ("java.lang.String".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), query.getQuery());

			} else if ("java.lang.Integer".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));

			} else if ("java.lang.Double".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
			} else if ("java.util.Date".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
			}
		}
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" select * from ( ");
		sql.append(" select aa.*,'' contractId  from ( ");
		// sql.append(" select c.id,c.expired_d expiredD,c.create_dt createDt,c.bd_account bdAccount,c.bd_account creator,a.modifier,a.modified_dt modifiedDt,b.id custId,a.id companyId,a.name,a.abbr_name abbrName,a.full_name fullName,if(length(trim(a.name))=0,a.full_name,a.name) disName,a.fax,a.size,a.industry,a.property,a.type,a.tel,a.website,a.location,a.address,a.postcode,a.profile,a.capital,a.background from company a,customer b,customer_bd c where a.id=b.company_id and c.cust_id=b.id ");
		sql.append(" select distinct c.id,c.expired_d expiredD,c.create_dt createDt,c.bd_account bdAccount,c.bd_account creator,a.modifier,a.modified_dt modifiedDt,b.id custId,a.id companyId,a.name,a.abbr_name abbrName,a.full_name fullName,if(length(trim(a.name))=0,a.full_name,a.name) disName,a.fax,a.size,a.industry,a.property,a.type,a.tel,a.website,a.location,a.address,a.postcode,a.profile,a.capital,a.background from customer b inner join company a on a.id=b.company_id and a.is_delete=0   ");
		sql.append(" left join (select * from (select a.id,a.cust_id,a.bd_account,a.expired_d,a.create_dt from customer_bd a  order by a.create_dt desc ) bd group by bd.cust_id) c on c.cust_id=b.id  ");
		sql.append(" where 1=1 and a.is_delete=0 and a.industry <> '-1' ");
		if (isNotEmpty(query.getIsAll())) {
			// 只要曾经是客户的都可以
			sql.append(" and  ( ");
			sql.append("  c.expired_d >=now() ");
			sql.append(" or  exists (select 1 from  customer_contract d where  b.id=d.cust_id ) ");
			sql.append(" or  exists (select 1 from  customer_contract_service_company d1 where  b.id=d1.cust_id ) ");
			sql.append("  ) ");
		} else {
			// BD客户(签约过期)
			sql.append(" and not exists  (select 1 from (select  distinct  a.cust_id from  customer_contract a where ifnull(a.end_d,now()) >=now() union all  select  distinct  b.cust_id  from customer_contract_service_company b ,customer_contract c where ifnull(c.end_d,now()) >=now() and b.contract_id=c.id) t1 where  t1.cust_id = b.id ) ");
			sql.append(" and (exists (select 1 from customer_bd t where ifnull(t.expired_d,now()) >=now() and t.cust_id = b.id  ) ");
			sql.append(" or exists (select 1 from customer_contract t7 where t7.end_d < now() and t7.cust_id = b.id ) ");
			sql.append(" or exists ( select 1 from ");
			sql.append(" ( select distinct t3.cust_id  from customer_contract_service_company  t3  ");
			sql.append(" where not exists (select 1 from customer_contract t4 where ifnull(t4.end_d,now()) >= now() and t4.id = t3.contract_id )  ");
			sql.append(" )	bb where  bb.cust_id = b.id ");
			sql.append(" ))");
		}
		sql.append(" ) aa ");

		sql.append(" ) t where 1=1 ");
		if (isNotEmpty(query.getId())) {
			sql.append(" and  t.id = ? ");
			values.add(query.getId());

		}
		if (isNotEmpty(query.getCompanyId())) {
			sql.append(" and  t.companyId = ? ");
			values.add(query.getCompanyId());

		}
		if (isNotEmpty(query.getName())) {
			sql.append(" and  ( t.name like ? or t.fullName like ? or t.abbrName like ? ) ");
			values.add("%" + query.getName() + "%");
			values.add("%" + query.getName() + "%");
			values.add("%" + query.getName() + "%");

		}
		if (isNotEmpty(query.getIndustry())) {
			sql.append(" and  t.industry = ? ");
			values.add(query.getIndustry());
		}
		if (isNotEmpty(query.getProperty())) {
			sql.append(" and  t.property = ? ");
			values.add(query.getProperty());
		}
		if (isNotEmpty(query.getType())) {
			sql.append(" and  t.type = ? ");
			values.add(query.getType());
		}
		if (isNotEmpty(query.getTel())) {
			sql.append(" and  t.tel like ? ");
			values.add("%" + query.getTel() + "%");

		}
		if (isNotEmpty(query.getWebsite())) {
			sql.append(" and  t.website like ? ");
			values.add("%" + query.getWebsite() + "%");

		}
		if (isNotEmpty(query.getLocation())) {
			sql.append(" and  t.location = ? ");
			values.add(query.getLocation());

		}

		if (isNotEmpty(query.getAddress())) {
			sql.append(" and  t.address like ? ");
			values.add("%" + query.getAddress() + "%");

		}
		if (isNotEmpty(query.getPostcode())) {
			sql.append(" and  t.postcode like ? ");
			values.add("%" + query.getPostcode() + "%");

		}
		if (isNotEmpty(query.getProfile())) {
			sql.append(" and  t.profile like ? ");
			values.add("%" + query.getProfile() + "%");

		}
		if (isNotEmpty(query.getIntegrity())) {
			// integrity
			sql.append(" and  t.integrity = ? ");
			values.add(query.getIntegrity());
		}
		if (isNotEmpty(query.getExintegrity())) {
			sql.append(" and  not ifnull(t.integrity,0) in (").append(query.getExintegrity()).append(") ");
		}

		if (isNotEmpty(query.getCreator())) {
			sql.append(" and  t.creator = ? ");
			values.add(query.getCreator());
		}
		if (isNotEmpty(query.getCreateDtBegin())) {
			sql.append(" and  t.createDt >= ? ");
			values.add(query.getCreateDtBegin());
		}
		if (isNotEmpty(query.getCreateDtEnd())) {
			sql.append(" and  t.createDt < ? ");
			values.add(DateHelper.add(query.getCreateDtEnd(), DateHelper.DAY, 1));
		}
		// .a.modifier modifiedDt
		if (isNotEmpty(query.getModifier())) {
			// product
			sql.append(" and  t.modifier = ? ");
			values.add(query.getModifier());
		}
		if (isNotEmpty(query.getModifiedDtBegin())) {
			// product
			sql.append(" and  t.modifiedDt >= ? ");
			values.add(query.getModifiedDtBegin());
		}
		if (isNotEmpty(query.getModifiedDtEnd())) {
			// product
			sql.append(" and  t.modifiedDt < ? ");
			values.add(DateHelper.add(query.getModifiedDtEnd(), DateHelper.DAY, 1));
		}

		if (query.getOnlineUser() != null && query.getIsAuth()) {
			log.info("你疯了.....");
			String authsql = AuthSqlUtil.authBDCustomerSQL(query.getOnlineUser().getUsername(), "t.custId", "t.bdAccount");
			if (!StringHelper.isEmpty(authsql))
				sql.append(authsql);
		} else {
			log.info("我疯了.....");
		}
		// System.out.println("sql:"+sql.toString());

		if (isNotEmpty(query.getSortColumns())) {
			sql.append(" order by  t.").append(query.getSortColumns());
		} else {
			sql.append(" order by  t.companyId desc  ");
		}
		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	private String authSQL(String username, String custIdColumn, String creatorColumn) {
		// admin不受控制
		if (ConstantDic.ADMIN_USER.equals(username))
			return "";

		SystemUser systemUser = SystemUserCache.getSystemUserByUsername(username);

		StringBuffer sb = new StringBuffer();

		// Account 角色 看全部
		// GM 看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_ACCOUNT) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_GM) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_SUPER_USER))
			return "";

		// CONTRACTOR看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_CONTRACTOR))
			return "";

		sb.append(" and (");
		sb.append(creatorColumn).append("='").append(username).append("'");
		sb.append(" or exists ( select 1 from customer_bd t3 where  t3.cust_id=").append(custIdColumn).append("  and t3.bd_account= '").append(username).append("' ) ");
		// 客户职位相关人 看属于自己的职位客户的
		sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id=t.id and t1.id=t2.job_id and t2.account= '").append(username).append("' ) ");
		// 下级创建的
		// sb.append(" or ").append(creatorColumn).append(" in ( select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) )");
		// 合同相关人员可以看合同相关的客户（包括服务公司客户）
		sb.append(" or exists ( select 1 from customer_contract t1,customer_contract_staff t2 where  t1.cust_id=").append(custIdColumn).append(" and t1.id=t2.contract_id and  t2.account='").append(username).append("' ) ");
		sb.append(" or exists ( select 1 from customer_contract t1,customer_contract_staff t2,customer_contract_service_company t3 where  t3.cust_id=").append(custIdColumn).append(" and t1.id=t2.contract_id and t1.id=t3.contract_id and  t2.account='").append(username).append("' ) ");
		// 下级是合同相关人员的（包括服务公司客户）
		// avlin..2013-1-5 15:05:27
		sb.append(" or exists ( select 1 from customer_contract t1,customer_contract_staff t2 where  t1.cust_id=").append(custIdColumn).append(" and t1.id=t2.contract_id and t2.account in (").append(" select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) ").append(" ) ) ");
		// avlin..2013-1-5 15:05:27
		sb.append(" or exists ( select 1 from customer_contract t1,customer_contract_staff t2,customer_contract_service_company t3 where  t3.cust_id=").append(custIdColumn).append(" and t1.id=t2.contract_id and t1.id=t3.contract_id and  t2.account in (").append(" select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) ").append(" ) ) ");
		// 下级是职位相关人员的客户
		// select b3.account from system_user b3 where title_id in(select b1.id
		// from system_branch_title b1 where b1.id in(select b2.title_id from
		// system_user b2 where b2.account='").append(username).append("'))
		// avlin..2013-1-5 15:05:27
		sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id=t.id and t1.id=t2.job_id and t2.account in (").append(" select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) ").append(" ) ) ");

		sb.append(" )");

		return sb.toString();

	}

	/**
	 * 为了区分 直接客户 间接客户 所有客户
	 * 
	 * @param username
	 * @param custIdColumn
	 * @param creatorColumn
	 * @param controle
	 * @return
	 */
	private String authSQL(String username, String custIdColumn, String creatorColumn, String controle) {
		// admin不受控制
		if (ConstantDic.ADMIN_USER.equals(username))
			return "";

		SystemUser systemUser = SystemUserCache.getSystemUserByUsername(username);

		StringBuffer sb = new StringBuffer();

		// Account 角色 看全部
		// GM 看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_ACCOUNT) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_GM) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_SUPER_USER))
			return "";

		// Contractor看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_CONTRACTOR))
			return "";

		sb.append(" and (");
		// BM 可以查看自己区域的 ROLE_Branch_Manager
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_Branch_Manager)) {

			// sb.append("  exists ( select 1 from customer_contract t1,customer_contract_staff t2 where  t1.cust_id=").append(custIdColumn);
			// sb.append("  and t1.id=t2.contract_id and t2.account in ");
			// sb.append(" (select b.account from system_user b where b.branch_company_id = ");
			// sb.append(" (select ss1.branch_company_id from system_user ss1 where ss1.account='");
			// sb.append(username).append("')))");

			sb.append("  exists ( select 1 from customer_contract t1,customer_contract_staff t2 where  t1.cust_id=").append(custIdColumn);
			sb.append("  and t1.id=t2.contract_id and t2.account in ");
			sb.append(" ( ").append(AuthSqlUtil.getSameBranchCompanyStaffSql(username)).append(" ))");

			// append(" select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) ").append(" ) ) ");
			// sb.append("  exists (select 1 from customer_contract_staff a ,customer_contract b  where b.contract_id = ").append(custIdColumn).append(" and a.account in (select account from system_user b where b.branch_company_id=").append("(select ss1.branch_company_id from system_user ss1 where ss1.account='").append(username).append("')) )");
		} else {
			// 同组别 成员可见

			// sb.append("  exists ( select 1 from customer_contract t1,customer_contract_staff t2  where  t1.cust_id=").append(custIdColumn);
			// sb.append(" and t1.id=t2.contract_id and t2.account in ");
			// sb.append(" (select b.account from system_user b where b.dept_id = ");
			// sb.append(" (select ss1.dept_id from system_user ss1 where ss1.account='");
			// sb.append(username).append("')))");

			sb.append("  exists ( select 1 from customer_contract t1,customer_contract_staff t2  where  t1.cust_id=").append(custIdColumn);
			sb.append(" and t1.id=t2.contract_id and t2.account in ");
			sb.append(" ( ").append(AuthSqlUtil.getSameDeptStaffSql(username)).append(" ))");

			// sb.append(" and  exists(select 1 from  (select b.company_id from customer b inner join (( select a.id, a.cust_id from customer_contract a where a.end_d > now() group by a.cust_id ) union all ( select b.contract_id, b.cust_id from customer_contract a, customer_contract_service_company b where a.end_d > now() and a.id = b.contract_id group by b.cust_id )) c on b.id = c.cust_id) a2 where a2.company_id=t.id ) ");
			// 合同相关人员可以看合同相关的客户（包括服务公司客户）
			// sb.append(" or exists ( select 1 from customer_contract t1,customer_contract_staff t2 where  t1.cust_id=").append(custIdColumn).append(" and t1.id=t2.contract_id and  t2.account='").append(username).append("' ) ");

			// sb.append(" or exists ( select 1 from customer_contract t1,customer_contract_staff t2,customer_contract_service_company t3 where  t3.cust_id=").append(custIdColumn).append(" and t1.id=t2.contract_id and t1.id=t3.contract_id and  t2.account in (");
			// sb.append(" (select b.account from system_user b where b.dept_id = ");
			// sb.append(" (select ss1.dept_id from system_user ss1 where ss1.account='");
			// sb.append(username).append("'))))");

			sb.append(" or exists ( select 1 from customer_contract t1,customer_contract_staff t2,customer_contract_service_company t3 where  t3.cust_id=").append(custIdColumn).append(" and t1.id=t2.contract_id and t1.id=t3.contract_id and  t2.account in (");
			sb.append("  ").append(AuthSqlUtil.getSameDeptStaffSql(username)).append(" ))");

			// .append(username).append("' ) ");
			// sb.append("  exists (select 1 from customer_contract_staff a11 where a11.contract_id = ").append(custIdColumn).append(" and a11.account in (select account from system_user b11 where b11.dept_id=").append("(select ss1.dept_id from system_user ss1 where ss1.account='").append(username).append("')) )");
		}
		/*
		 * if("DIRECT".equals(controle)){ //签约客户是自己创建的
		 * sb.append(creatorColumn).append("='").append(username).append("'");
		 * //签约客户 是自己BD的
		 * sb.append(" or exists ( select 1 from customer_bd t3 where  t3.cust_id="
		 * )
		 * .append(custIdColumn).append("  and t3.bd_account= '").append(username
		 * ).append("' ) "); //客户职位相关人 看属于自己的职位客户的 sb.append(
		 * " or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id=t.id and t1.id=t2.job_id and t2.account= '"
		 * ).append(username).append("' ) "); //下级创建的 //sb.a
		 * ppend(" or ").append(creatorColumn).append(
		 * " in ( select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='"
		 * ).append(username).append(
		 * "'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='"
		 * ).append(username).append("')) )"); //合同相关人员可以看合同相关的客户（包括服务公司客户）
		 * sb.append(
		 * " or exists ( select 1 from customer_contract t1,customer_contract_staff t2 where  t1.cust_id="
		 * ).append(custIdColumn).append(
		 * " and t1.id=t2.contract_id and  t2.account='"
		 * ).append(username).append("' ) "); sb.append(
		 * " or exists ( select 1 from customer_contract t1,customer_contract_staff t2,customer_contract_service_company t3 where  t3.cust_id="
		 * ).append(custIdColumn).append(
		 * " and t1.id=t2.contract_id and t1.id=t3.contract_id and  t2.account='"
		 * ).append(username).append("' ) "); }else
		 * if("INDIRECT".equals(controle)){ //下级是合同相关人员的（包括服务公司客户）
		 * //avlin..2013-1-5 15:05:27 sb.append(
		 * "  exists ( select 1 from customer_contract t1,customer_contract_staff t2 where  t1.cust_id="
		 * ).append(custIdColumn).append(
		 * " and t1.id=t2.contract_id and t2.account in (").append(
		 * " select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='"
		 * ).append(username).append(
		 * "'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='"
		 * ).append(username).append("')) ").append(" ) ) "); //avlin..2013-1-5
		 * 15:05:27 sb.append(
		 * " or exists ( select 1 from customer_contract t1,customer_contract_staff t2,customer_contract_service_company t3 where  t3.cust_id="
		 * ).append(custIdColumn).append(
		 * " and t1.id=t2.contract_id and t1.id=t3.contract_id and  t2.account in ("
		 * ).append(
		 * " select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='"
		 * ).append(username).append(
		 * "'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='"
		 * ).append(username).append("')) ").append(" ) ) "); //下级是职位相关人员的客户
		 * //select b3.account from system_user b3 where title_id in(select
		 * b1.id from system_branch_title b1 where b1.id in(select b2.title_id
		 * from system_user b2 where b2.account='").append(username).append("'))
		 * //avlin..2013-1-5 15:05:27 sb.append(
		 * " or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id=t.id and t1.id=t2.job_id and t2.account in ("
		 * ).append(
		 * " select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='"
		 * ).append(username).append(
		 * "'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='"
		 * ).append(username).append("')) ").append(" ) ) "); //sb.append(
		 * " or  exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id=t.id "
		 * ).append("  and t1.id=t2.job_id and t2.account in (").append(
		 * " select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='"
		 * ).append(username).append(
		 * "'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account ='"
		 * ).append(username).append("')) ").append(" ) ) "); //sb.append(
		 * " and  not  exists ( select 1 from contract_job t1  where  t1.cust_id=t.id "
		 * ).append(" and t1.job_manager ='").append(username).append("')"); }
		 */

		sb.append(" )");

		return sb.toString();

	}

}
